#coding=utf-8
import pandas as pd
from IPython.display import display

def compareExcel(file1,file2,compare_col):
    # 读取文件
    df1 = pd.read_excel(file1)
    df2 = pd.read_excel(file2)

    # 合并两个DataFrame
    merged = df1.merge(df2, on=compare_col, how='outer', indicator=True)

    # 找出只在其中一个文件存在的项
    only_in_file1 = merged[merged['_merge'] == 'left_only'][compare_col]
    only_in_file2 = merged[merged['_merge'] == 'right_only'][compare_col]

    # 找出两列都存在的不同值（如果有多列需要对比）
    if 'B列' in df1.columns and 'B列' in df2.columns:
        common = merged[merged['_merge'] == 'both']
        diff_values = common[common['B列_x'] != common['B列_y']]

        print("\n相同键但不同值的记录：")
        print(diff_values[[compare_col, 'B列_x', 'B列_y']].rename(
            columns={'B列_x': 'file1的值', 'B列_y': 'file2的值'}))

    # 输出结果
    print("\n只存在于file1.xlsx的项：")
    print(only_in_file1.to_frame(name=compare_col))

    print("\n只存在于file2.xlsx的项：")
    print(only_in_file2.to_frame(name=compare_col))





def compare_excel_columns(file1, file2, sheet_name=None, col_name='A列'):
    """ 对比两个Excel文件的指定列，返回差异报告 """

    # 读取数据（支持指定sheet）
    df1 = pd.read_excel(file1, sheet_name=sheet_name)
    df2 = pd.read_excel(file2, sheet_name=sheet_name)

    # 添加来源标记
    df1['_source'] = 'file1'
    df2['_source'] = 'file2'

    # 合并数据
    combined = pd.concat([df1, df2], ignore_index=True)

    # 找出重复值（即两个文件都存在的值）
    duplicates = combined[combined.duplicated(subset=[col_name], keep=False)]

    # 找出唯一值（只存在于一个文件的值）
    uniques = combined.drop_duplicates(subset=[col_name], keep=False)

    # 生成对比报告
    report = {
        'total_in_file1': len(df1),
        'total_in_file2': len(df2),
        'common_items': len(duplicates) // 2,
        'unique_to_file1': uniques[uniques['_source'] == 'file1'][col_name].tolist(),
        'unique_to_file2': uniques[uniques['_source'] == 'file2'][col_name].tolist(),
        'common_but_different': []
    }

    # 检查共同项的其他列是否相同
    if len(df1.columns) > 1 and len(df2.columns) > 1:
        common_cols = set(df1.columns) & set(df2.columns) - {col_name, '_source'}
        for col in common_cols:
            diff_mask = duplicates.groupby(col_name)[col].nunique() > 1
            diff_items = diff_mask[diff_mask].index.tolist()
            if diff_items:
                report['common_but_different'].append({
                    'column': col,
                    'items': diff_items
                })

    return report





if __name__ == '__main__':

    file1=r"E:\项目相关\13.可再生能源补贴\26.补贴项目功能扩展三期\12.部署\tables_202506281030.xlsx"
    file2=r"E:\项目相关\13.可再生能源补贴\26.补贴项目功能扩展三期\12.部署\tables_202506281028.xlsx"


    # 设置对比列（可修改）
    col_name = 'TABLE_NAME'
    # compareExcel(file1,file2,col_name)

    sheet_name = "sheet"
    result =compare_excel_columns(file1, file2, sheet_name=sheet_name, col_name=col_name)
    # 使用示例
    # result = compare_excel_columns(file1, file2, col_name)
    print("对比结果：")
    print(f"文件1总记录数: {result['total_in_file1']}")
    print(f"文件2总记录数: {result['total_in_file2']}")
    print(f"共同项数量: {result['common_items']}")

    print("\n只存在于file1的项：")
    print(result['unique_to_file1'])

    print("\n只存在于file2的项：")
    print(result['unique_to_file2'])

    if result['common_but_different']:
        print("\n共同项但其他列不同的记录：")
        for diff in result['common_but_different']:
            print(f"列名: {diff['column']}")
            print(f"差异项: {diff['items']}")